﻿/*****************************************************************************/
/* Stored Procedure :: GrantsFor                                             */
/*****************************************************************************/
CREATE PROCEDURE [GrantManagement].[GrantsFor]
(
	@VolunteerID BIGINT,
	@SortBy varchar(10) = 'LASTNAME',
	@SortDirection char = 'A',
	@StartRow int = 0,
	@MaxRows int = 50
)
AS  
BEGIN

	WITH OrderedGrants AS
	(
		SELECT Grants.OID, 
				CASE
                   WHEN @SortBy = 'APPLICANT' AND @SortDirection <> 'D' THEN (row_number() OVER (ORDER BY Applicant.FirstName + ' ' + Applicant.LastName))
                   WHEN @SortBy = 'APPLICANT' AND @SortDirection = 'D' THEN (row_number() OVER (ORDER BY Applicant.FirstName + ' ' + Applicant.LastName DESC))
                   WHEN @SortBy = 'FIRSTNAME' AND @SortDirection <> 'D' THEN (row_number() OVER (ORDER BY ServiceMan.FirstName, ServiceMan.LastName))
                   WHEN @SortBy = 'FIRSTNAME' AND @SortDirection = 'D' THEN (row_number() OVER (ORDER BY ServiceMan.FirstName DESC, ServiceMan.LastName))
                   WHEN @SortBy = 'LASTNAME' AND @SortDirection <> 'D' THEN (row_number() OVER (ORDER BY ServiceMan.LastName, ServiceMan.FirstName))
                   WHEN @SortBy = 'LASTNAME' AND @SortDirection = 'D' THEN (row_number() OVER (ORDER BY ServiceMan.LastName DESC, ServiceMan.FirstName))
                   WHEN @SortBy = 'VALUE' AND @SortDirection <> 'D' THEN (row_number() OVER (ORDER BY Grants.ValueOfGrant))
                   WHEN @SortBy = 'VALUE' AND @SortDirection = 'D' THEN (row_number() OVER (ORDER BY Grants.ValueOfGrant DESC))
                END as 'RowID'
		FROM  Grants
		INNER JOIN Cases On Grants.CaseID = Cases.OID
		INNER JOIN People ServiceMan On Cases.ServiceManID = ServiceMan.OID
		INNER JOIN People Applicant On Grants.ApplicantID = Applicant.OID
		WHERE ([GrantManagement].[fnCanViewGrant](Grants.OID, @VolunteerID) = 1)
	)
	SELECT  Grants.OID as 'GrantsOID', 
			Grants.Version as 'GrantsVersion', 
			Grants.CaseID, 
			Grants.ApplicantID, 
			Grants.ValueOfGrant, 
			Grants.CheckNumber, 
			Grants.NameOnCreditCard, 
			Grants.GrantTypeID, 
			Grants.StateID, 
			Grants.PaymentTypeID, 
			Grants.DeliveryMethodID, 
			Grants.DeliverToPersonID, 
			Grants.DeliverToAddressID, 
			Grants.Approver1, 
			Grants.ApprovalDate1, 
			Grants.Approver2, 
			Grants.ApprovalDate2, 
			Grants.DisbursedBy, 
			Grants.DispersalDate, 
			Grants.VerifiedBy, 
			Grants.VerificationDate,
			Grants.UpdatedDate as 'GrantsUpdatedDate',
			Grants.CreatedDate as 'GrantsCreatedDate',

			Cases.OID as 'CaseOID', 
			Cases.Version as 'CaseVersion', 
			Cases.ServiceManID, 
			Cases.CaseVolunteerID, 
			Cases.CaseDirectorID,
			Cases.InjuryDate,

			ServiceMen.OID as 'ServiceMenOID', 
			ServiceMen.Version as 'ServiceMenVersion', 
			ServiceMen.PrimaryAddressID as 'ServiceMenPrimaryAddressID', 
			ServiceMen.PermanentAddressID as 'ServiceMenPermanentAddressID',

			Applicants.OID as 'ApplicantsOID', 
			Applicants.Version as 'ApplicantsVersion', 
			Applicants.KinshipID, 
			Applicants.AddressID as 'ApplicantsAddressID', 

			People.OID as 'PeopleOID', 
			People.Version as 'PeopleVersion', 
			People.FirstName, 
			People.LastName, 
			People.MiddleInitial, 
			People.DOB, 
			People.Sex, 
			People.Phone1, 
			People.Phone2, 
			People.eMailAddress, 

			Users.OID as 'UsersOID', 
			Users.GUID, 
			Users.FullName, 
			Users.UPN, 
			Users.LastSignedIn

	FROM OrderedGrants
	INNER JOIN Grants On Grants.OID = OrderedGrants.OID
	INNER JOIN Cases On Grants.CaseID = Cases.OID
	INNER JOIN ServiceMen On ServiceMen.OID = Cases.ServiceManID
	INNER JOIN Applicants On Grants.ApplicantID = Applicants.OID
	INNER JOIN People On ServiceMen.OID = People.OID
	INNER JOIN Users On Users.OID = Cases.CaseVolunteerID
	WHERE
		RowID BETWEEN @StartRow+1 AND (@StartRow+@MaxRows)
	ORDER BY RowID

END


